package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import es.ats.codington.festival.utils.JSonQuery;
import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.interfaces.daos.IVisitorDAO;

public class VisitorDAOImpl implements IVisitorDAO {
	
	//----------------------------------------
	
	private Connection connection = null;
	private PreparedStatement statement = null;
	private ResultSet resultSet = null;
	//JSonQuery json=new JSonQuery();
	
	/**
	 * Constructor
	 * @param connection
	 */
	public VisitorDAOImpl(Connection connection) {
		this.connection = connection;
	}
	
	//---------------------- Methods-----------------------------
	
	/**
	 * @param u
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public boolean registerNewVisitorDAO(Visitor u) throws SQLException, ClassNotFoundException{

		int affectedRows1 = 0,affectedRows2=0,totalRows=0;

		try{
				              
			//statement = connection.prepareStatement(json.getRegisterNewPeople());
			statement = connection.prepareStatement("INSERT INTO people (firstName,lastName,email,phone,address,dni)VALUES (?,?,?,?,?,?)", statement.RETURN_GENERATED_KEYS);
			
			statement.setString(1, u.getFirstName());
			statement.setString(2, u.getLastName());
			statement.setString(3, u.getEmail());
			statement.setLong(4, u.getPhone());
			statement.setString(5, u.getAddress());
			statement.setString(6, u.getDni());
			
			affectedRows1 =statement.executeUpdate();
			
			ResultSet keys = statement.getGeneratedKeys();
			keys.next();
			int key =keys.getInt(1);
			u.setIdVisitor(key);
			//statement = connection.prepareStatement(json.getRegisterNewVisitor());
			statement = connection.prepareStatement("INSERT INTO visitor (idVisitor,visitorName,password)VALUES (?,?,?)");
			
			statement.setInt(1, key);
			statement.setString(2, u.getVisitorName());
			statement.setString(3, u.getPassword());
			
			affectedRows2 =statement.executeUpdate();
			totalRows=affectedRows1+affectedRows2;


		}catch (Exception ee) {
			System.out.println(" registerNewVisitorDAO 1: "+ ee.getMessage());
			return false;

		}
		if(totalRows!=0)
		{
			return true;
		}
		else
		{
			return false;
		}
		
	}
	
	/**
	 * @param u
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public boolean updateVisitorDAO(Visitor u) throws ClassNotFoundException,
	SQLException {
		
		int affectedRows1 = 0,affectedRows2=0,totalRows=0;
		
		try{
		
		//	statement = connection.prepareStatement(json.getUpdatePeople());
			
			statement = connection.prepareStatement("UPDATE people SET firstName=?,lastName=?,email=?,phone=?,address=?,dni=? WHERE idPeople=?");
			
			statement.setString(1, u.getFirstName());
			statement.setString(2, u.getLastName());
			statement.setString(3, u.getEmail());
			statement.setLong(4, u.getPhone());
			statement.setString(5, u.getAddress());
			statement.setString(6, u.getDni());
			statement.setInt(7, u.getIdVisitor());
			
			affectedRows1 =statement.executeUpdate();
			
		//	statement = connection.prepareStatement(json.getUpdateVisitor());
			statement = connection.prepareStatement("UPDATE visitor SET visitorName=?,password=? WHERE idVisitor=?");
			
			statement.setString(1, u.getVisitorName());
			statement.setString(2, u.getPassword());		
			statement.setInt(3,u.getIdVisitor());
			
			
			affectedRows2 =statement.executeUpdate();
			
			totalRows=affectedRows1+affectedRows2;
			
		}
		catch(Exception ee) {
			System.out.println(" UpdateVisitorDAO 1: "+ ee.getMessage());
			return false;

		}
		
		if(totalRows!=0)
		{
			return true;
		}
		else
		{
			return false;
		}
		
		}
	
	/**
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public int getIdVisitorDAO(Visitor u) throws SQLException{
		int idVisitor = 0;

		try{		    	
						
		//	statement = connection.prepareStatement(json.getGetIdVisitor());
			statement = connection.prepareStatement("SELECT idVisitor FROM visitor WHERE visitorName=?");
			
			statement.setString(1, u.getVisitorName());
			
			ResultSet keys = statement.executeQuery();
			keys.next();
			idVisitor =keys.getInt(1);
			
		
		} catch (Exception ee) {
			System.out.println("getIdVisitorDAO: "+ ee.getMessage());
			

		} 
		return idVisitor;
	}
	
	/**
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public boolean loginDAO(Visitor u) throws SQLException{
		int fila =0;
		try{	
			
			//statement = connection.prepareStatement(json.getLoginVisitor());
			
			statement = connection.prepareStatement("SELECT idVisitor FROM visitor WHERE visitorName=? AND password=?");
			
			statement.setString(1, u.getVisitorName());
			statement.setString(2, u.getPassword());

			resultSet = statement.executeQuery();
			
			resultSet.last();
			fila = resultSet.getRow();
			resultSet.beforeFirst();
						
		}catch(Exception eee){
			eee.getMessage();
		} 
			
		return fila==1;
	}
	
	/**
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public boolean isAdminDAO(Visitor u) throws SQLException{
		boolean isAdmin=false;
		try{	
			
		//	statement = connection.prepareStatement(json.getIsAdmin());
			statement = connection.prepareStatement("SELECT isAdmin FROM visitor WHERE visitorName=? AND password=?");
			
			statement.setString(1, u.getVisitorName());
			statement.setString(2, u.getPassword());

			ResultSet keys = statement.executeQuery();
			keys.next();
			isAdmin =keys.getBoolean(1);
						
		}catch(Exception eee){
			eee.getMessage();
		} 
			
		return isAdmin;
	}
	
	/**
	 * 
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public boolean unregisterVisitorDAO(Visitor u) throws SQLException{
		int affectedRows1 = 0, affectedRows2=0,totalRows=0;

		try{		    		
		
		//	statement = connection.prepareStatement(json.getdeleteVisitor());
			statement = connection.prepareStatement("DELETE FROM visitor WHERE idVisitor=?");
			
			
			statement.setInt(1, u.getIdVisitor());

			affectedRows1 = statement.executeUpdate();
			
		//	statement = connection.prepareStatement(json.getdeletePeople());
			
			
			statement.setInt(1, u.getIdVisitor());

			affectedRows2 = statement.executeUpdate();

		} catch (Exception ee) {
			System.out.println("unregisterVisitorForEvent 1: "+ ee.getMessage());
			return false;

		} 

		totalRows=affectedRows1+affectedRows2;
		if(totalRows!=0)
		{
			return true;
		}
		else
		{
			return false;
		}
	}

	
	
	/**
	 * @param u
	 * @return
	 * @throws SQLException
	 */
	public Visitor getVisitorDAO(Visitor u) throws SQLException{

		try{		    	
			
		//	statement = connection.prepareStatement(json.getGetVisitor());
			statement = connection.prepareStatement("SELECT * FROM people WHERE idPeople=?");
			
			statement.setInt(1, u.getIdVisitor());
			
			
			ResultSet resultset = statement.executeQuery();
			

			
			resultset.next();

			u.setFirstName(resultset.getString("firstName"));

			u.setLastName(resultset.getString("lastName"));

			u.setEmail(resultset.getString("email"));
	
			u.setPhone(resultset.getLong("phone"));
		
			u.setAddress(resultset.getString("address"));
		
			u.setDni(resultset.getString("dni"));
			
		
			
			statement = connection.prepareStatement("SELECT * FROM visitor WHERE idVisitor=?");
			
	
			
			statement.setInt(1, u.getIdVisitor());
			
			resultset = statement.executeQuery();
			
	
			
			resultset.next();
			u.setVisitorName(resultset.getString("visitorName"));
			u.setPassword(resultset.getString("password"));
			//u.setIsAdmin(resultset.getBoolean("isAdmin"));
			
	
		
		} catch (Exception ee) {
			System.out.println("getVisitorDAO: "+ ee.getMessage());
			

		} 
		return u;	
		}
}
